﻿using Dapper;
using DataServer.Entities;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace DataServer.MySql
{
    public class MenuDao
    {
        DapperEx dapperex = new DapperEx();
        private IDbConnection dbConnection;

        private IDbConnection DbConnection
        {
            get
            {
                dbConnection = new MySqlConnection(ConstConn.conn);

                bool isClosed = dbConnection.State == ConnectionState.Closed;
                if (isClosed) dbConnection.Open();
                return dbConnection;
            }
        }
        public IEnumerable<Menu> GetMenuByCode(String userid, String menucode)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(String.Format(@"SELECT M.*,U.UserId FROM  MENU M JOIN MENUGROUP MG ON M.MenuId=MG.MenuName JOIN USERGROUP UG ON MG.MenunGroupName=UG.MenuGroup JOIN USERPROFILE U ON U.AuthGroup=UG.AuthGroupName
WHERE   U.ISACTIVE = 1 AND U.UserId =?UserId AND  length(m.menuid)>1 and m.MenuId like concat(?MenuId,'%') order by m.MenuId ") );
            Menu menu = new Menu();
            menu.MenuId = menucode;
            IEnumerable<Menu> lsmenu = dapperex.Query<Menu>(sb.ToString(), new { MenuId = menu.MenuId, UserId = userid });
            return lsmenu;
        }
        public IEnumerable<Menu> QueryMenus(String strmenuid, String strmenuname)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(String.Format(@"SELECT M.*  FROM  MENU M  WHERE   m.MenuId like concat(?MenuId,'%') AND m.MenuName like concat(?MenuName,'%') order by m.MenuId "));
            Menu menu = new Menu();
            menu.MenuId = strmenuid;
            menu.MenuName = strmenuname;
            IEnumerable<Menu> lsmenu = dapperex.Query<Menu>(sb.ToString(), new { MenuId = menu.MenuId, MenuName = menu.MenuName });
            return lsmenu;
        }

        public Menu GetMenuColumnName(String strcolumnname, String strvalue)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM menu u where u." + strcolumnname + "=?" + strcolumnname + " ");
            //UserProfile param = new UserProfile();
            //param.UserId = userid;
            //param.Pwd = pwd;
            Menu menu = new Menu();
            switch (strcolumnname)
            {
                case "MenuId":
                    menu = dapperex.FirstOrDefault<Menu>(sb.ToString(), new { MenuId = strvalue });
                    break;
                case "MenuName":
                    menu = dapperex.FirstOrDefault<Menu>(sb.ToString(), new { MenuName = strvalue });
                    break;
                default:
                    break;
            }

            return menu;
        }

        public Boolean UpdateMenus(List<DataServer.Entities.Menu> lsadd, List<DataServer.Entities.Menu> lsmodify, List<DataServer.Entities.Menu> lsdel,String struserid)
        {
            String straddsql = "insert into menu(MenuId,MenuName,HtmlName)values(?MenuId,?MenuName,?HtmlName)";
            String strupdatesql = " update menu set MenuName=?MenuName,HtmlName=?HtmlName WHERE MenuId=?MenuId ";
            String strdelsql = "delete from menu where MenuId=?MenuId";
            IDbTransaction tran = DbConnection.BeginTransaction();
            try
            {
                int result = 0;
                foreach (Menu mu in lsadd)
                {
                    result += DbConnection.Execute(straddsql, mu);
                }
                foreach(Menu mmu in lsmodify)
                {
                    result += DbConnection.Execute(strupdatesql, mmu);
                }
                foreach (Menu dmu in lsdel)
                {
                    result += DbConnection.Execute(strdelsql, dmu);
                }
                return true;
            }
            catch(Exception ex)
            {
                return false;
            }
                

        }
    }
}
